﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using WorkFlowSystem.DataBase;
using WorkFlowSystem.Common;

namespace WorkFlowSystem
{
    public partial class UserInfoUi : Form
    {
        public UserInfoUi()
        {
            InitializeComponent();
        }

        #region 辅助方法
        public void SetMode(bool modify_mode, string uid)
        {
            m_modify_mode = modify_mode;
            if (m_user_info == null)
            {
                m_user_info = new UserInfo();
                m_user_info.uid = uid;
            }
        }

        private void UserInfoUi_Load(object sender, EventArgs e)
        {
            if (m_modify_mode)
            {
                if (init_modify_user() < 0)
                    this.Close();
            }
            else
            {
                if (init_add_user() < 0)
                    this.Close();
            }
        }

        private int get_dpt_name()
        {
            DBOperator db_op = new DBOperator();
            if (db_op.ConnectDB() < 0)
            {
                MessageBox.Show("无法连接服务器，请检查网络连接");
                return -1;
            }
            string sql = "select d_name from department";
            DataTable dt = new DataTable();
            if (db_op.ExecuteDataTable(sql, null, false, ref dt) < 0)
            {
                Logger.WriteLog("get d_name from department fail");
                db_op.CloseDB();
                return -1;
            }
            db_op.CloseDB();
            for (int i = 0; i < dt.Rows.Count; i++) 
            {
                comboBoxEx3.Items.Add(dt.Rows[i]["d_name"].ToString());
            }
            return 0;
        }

        private int get_position_name()
        {
            DBOperator db_op = new DBOperator();
            if (db_op.ConnectDB() < 0)
            {
                MessageBox.Show("无法连接服务器，请检查网络连接");
                return -1;
            }
            string sql = "select p_name from position_info, department as d where p_did=d.did";
            //List<SqlParameter> para_list = new List<SqlParameter>();
            //para_list.Add(new SqlParameter("@did", m_user_info.user_department));
            DataTable dt = new DataTable();
            if (db_op.ExecuteDataTable(sql, null, false, ref dt) < 0)
            {
                Logger.WriteLog("get p_name from position_info fail");
                db_op.CloseDB();
                return -1;
            }
            db_op.CloseDB();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                comboBoxEx4.Items.Add(dt.Rows[i]["p_name"].ToString());
            }
            return 0;
        }

        private int load_userinfo()
        {
            DBOperator db_op = new DBOperator();
            if (db_op.ConnectDB() < 0)
            {
                MessageBox.Show("无法连接服务器，请检查网络连接");
                return -1;
            }
            string sql = "select * from user_info where uid=@uid";
            List<SqlParameter> para_list = new List<SqlParameter>();
            para_list.Add(new SqlParameter("@uid", m_user_info.uid));
            DataTable dt = new DataTable();
            if (db_op.ExecuteDataTable(sql, para_list, false, ref dt) < 0)
            {
                Logger.WriteLog("cannot find userinfo with uid");
                db_op.CloseDB();
                return -1;
            }
            m_user_info.user_name = dt.Rows[0]["u_name"].ToString();
            m_user_info.user_department = dt.Rows[0]["u_did"].ToString();
            m_user_info.user_position = dt.Rows[0]["u_pid"].ToString();
            m_user_info.user_sex = dt.Rows[0]["u_sex"].ToString();
            m_user_info.user_hire = dt.Rows[0]["u_hire_flag"].ToString();
            m_user_info.user_description = dt.Rows[0]["u_description"].ToString();
            m_user_info.user_salary = dt.Rows[0]["u_salary"].ToString();
            m_user_info.user_hire = dt.Rows[0]["u_hire_flag"].ToString();
            try
            {
                m_user_info.user_birthday = DateTime.Parse(dt.Rows[0]["u_birthday"].ToString());
            }
            catch (Exception ex) {
                Logger.WriteLogWarn("Parse u_birthday fail [" + ex.Message + "]");
            }
            //get d_name by did
            sql = "select d_name from department where did=@did";
            para_list.Clear();
            para_list.Add(new SqlParameter("@did", m_user_info.user_department));
            dt.Clear();
            if (db_op.ExecuteDataTable(sql, para_list, false, ref dt) < 0)
            {
                Logger.WriteLog("cannot find dpt_name with did");
                db_op.CloseDB();
                return -1;
            }
            if (dt.Rows.Count <= 0)
            {
                Logger.WriteLog("cannot find dpt_name with did");
                db_op.CloseDB();
                return -1;
            }
            m_user_info.dpt_name = dt.Rows[0]["d_name"].ToString();

            //get p_name by pid
            sql = "select p_name from position_info where pid=@pid";
            para_list.Clear();
            para_list.Add(new SqlParameter("@pid", m_user_info.user_position));
            dt.Clear();
            if (db_op.ExecuteDataTable(sql, para_list, false, ref dt) < 0)
            {
                Logger.WriteLog("cannot find p_name with pid");
                db_op.CloseDB();
                return -1;
            }
            if (dt.Rows.Count <= 0)
            {
                Logger.WriteLog("cannot find p_name with pid");
                db_op.CloseDB();
                return -1;
            }
            m_user_info.position_name = dt.Rows[0]["p_name"].ToString();

            sql = "select u_account, u_auth from user_login where uid=@uid";
            para_list.Clear();
            para_list.Add(new SqlParameter("@uid", m_user_info.uid));
            dt.Clear();
            if (db_op.ExecuteDataTable(sql, para_list, false, ref dt) < 0)
            {
                Logger.WriteLog("cannot find user_login info with uid");
                db_op.CloseDB();
                return -1;
            }
            db_op.CloseDB();
            m_user_info.user_account = dt.Rows[0]["u_account"].ToString();
            m_user_info.user_auth = dt.Rows[0]["u_auth"].ToString();
            return 0;
        }

        private int init_add_user()
        {
            get_position_name();
            get_dpt_name();
            comboBoxEx1.Items.Add("男");
            comboBoxEx1.Items.Add("女");
            comboBoxEx2.Items.Add("USER");
            comboBoxEx2.Items.Add("MANAGER");
            comboBoxEx2.Items.Add("BOSS");
            comboBoxEx2.Items.Add("ADMIN");
            comboBoxEx5.Items.Add("在职");
            comboBoxEx5.Items.Add("退职");
            return 0;
        }

        private int init_modify_user()
        {
            get_position_name();
            get_dpt_name();
            comboBoxEx1.Items.Add("男");
            comboBoxEx1.Items.Add("女");
            comboBoxEx2.Items.Add("USER");
            comboBoxEx2.Items.Add("MANAGER");
            comboBoxEx2.Items.Add("BOSS");
            comboBoxEx2.Items.Add("ADMIN");
            comboBoxEx5.Items.Add("在职");
            comboBoxEx5.Items.Add("退职");
            //load data from database to memory
            if (load_userinfo() < 0)
            {
                MessageBox.Show("加载用户信息失败");
                return -1;
            }
            textBoxX1.Text = m_user_info.user_name;
            textBoxX2.Text = m_user_info.user_account;
            for (int i = 0; i < comboBoxEx1.Items.Count; i++)
            {
                if (comboBoxEx1.Items[i].ToString() == m_user_info.user_sex)
                {
                    comboBoxEx1.SelectedIndex = i;
                    break;
                }
            }
            dateTimeInput1.Value = m_user_info.user_birthday;
            for (int i = 0; i < comboBoxEx4.Items.Count; i++)
            {
                if (comboBoxEx4.Items[i].ToString() == m_user_info.position_name)
                {
                    comboBoxEx4.SelectedIndex = i;
                    break;
                }
            }
            for (int i = 0; i < comboBoxEx2.Items.Count; i++)
            {
                if (comboBoxEx2.Items[i].ToString() == m_user_info.user_auth.ToString())
                {
                    comboBoxEx2.SelectedIndex = i;
                    break;
                }
            }
            for (int i = 0; i < comboBoxEx3.Items.Count; i++)
            {
                if (comboBoxEx3.Items[i].ToString() == m_user_info.dpt_name)
                {
                    comboBoxEx3.SelectedIndex = i;
                    break;
                }
            }
            for (int i = 0; i < comboBoxEx4.Items.Count; i++)
            {
                if (comboBoxEx4.Items[i].ToString() == m_user_info.position_name)
                {
                    comboBoxEx4.SelectedIndex = i;
                    break;
                }
            }
            for (int i = 0; i < comboBoxEx5.Items.Count; i++)
            {
                if (comboBoxEx5.Items[i].ToString() == m_user_info.user_hire)
                {
                    comboBoxEx5.SelectedIndex = i;
                    break;
                }
            }
            textBoxX3.Text = m_user_info.user_salary.ToString();
            richTextBoxEx1.Text = m_user_info.user_description;
            return 0;
        }
        #endregion 

        /// <summary>
        /// 提交
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void buttonX1_Click(object sender, EventArgs e)
        {
            m_user_info.user_name = textBoxX1.Text;
            m_user_info.user_account = textBoxX2.Text;
            m_user_info.user_salary = textBoxX3.Text;
            m_user_info.user_sex = (comboBoxEx1.SelectedItem == null) ? "" : comboBoxEx1.SelectedItem.ToString();
            m_user_info.user_birthday = dateTimeInput1.Value;  
            m_user_info.user_auth = (comboBoxEx2.SelectedItem == null) ? "" : comboBoxEx2.SelectedItem.ToString();
            m_user_info.dpt_name = (comboBoxEx3.SelectedItem == null) ? "" : comboBoxEx3.SelectedItem.ToString();
            m_user_info.position_name = (comboBoxEx4.SelectedItem == null) ? "" : comboBoxEx4.SelectedItem.ToString();
            m_user_info.user_description = richTextBoxEx1.Text;
            m_user_info.user_hire = (comboBoxEx5.SelectedItem == null) ? "" : comboBoxEx5.SelectedItem.ToString();

            DBOperator db_op = new DBOperator();
            if (db_op.ConnectDB() < 0)
            {
                MessageBox.Show("无法连接服务器，请检查网络连接");
                return;
            }
            string sql = "";
            List<SqlParameter> para_list = new List<SqlParameter>();
            DataTable dt = new DataTable();

            //获取部门id
            sql = "select did from department where d_name=@dpt_name";
            para_list.Add(new SqlParameter("@dpt_name", m_user_info.dpt_name));
            if (db_op.ExecuteDataTable(sql, para_list, false, ref dt) < 0)
            {
                Logger.WriteLog("cannot find did with this dpt_name");
                MessageBox.Show("无此部门的ID");
                db_op.CloseDB();
                return;
            }
            if (dt.Rows.Count <= 0)
            {
                Logger.WriteLog("cannot find did with this dpt_name");
                MessageBox.Show("无此部门的ID");
                db_op.CloseDB();
                return;
            }
            m_user_info.user_department = dt.Rows[0]["did"].ToString();

            //获取position id
            dt.Clear();
            para_list.Clear();
            sql = "select pid from position_info, department as d where p_name=@p_name and p_did=@did";
            para_list.Add(new SqlParameter("@p_name", m_user_info.position_name));
            para_list.Add(new SqlParameter("@did", m_user_info.user_department));
            if (db_op.ExecuteDataTable(sql, para_list, false, ref dt) < 0)
            {
                Logger.WriteLog("cannot find pid with this p_name and p_did");
                MessageBox.Show("无此职位信息");
                db_op.CloseDB();
                return;
            }
            if (dt.Rows.Count <= 0)
            {
                Logger.WriteLog("cannot find pid with this p_name and p_did");
                MessageBox.Show("无此职位信息");
                db_op.CloseDB();
                return;
            }
            m_user_info.user_position = dt.Rows[0]["pid"].ToString();

            if (!m_modify_mode)
            {
                //判断账号是否已存在
                dt.Clear();
                sql = "select * from user_login where u_account=@user_account";
                para_list.Add(new SqlParameter("@user_account", m_user_info.user_account));
                if (db_op.ExecuteDataTable(sql, para_list, false, ref dt) < 0)
                {
                    MessageBox.Show("获取用户信息失败");
                    db_op.CloseDB();
                    return;
                }
                if (dt.Rows.Count > 0)
                {
                    MessageBox.Show("账户已存在");
                    db_op.CloseDB();
                    return;
                }
                //insert into table Users
                dt.Clear();
                para_list.Clear();
                //use transaction
                sql = "insert into user_login(u_account, u_password, u_auth, u_insert_uid, u_insert_date) " +
                    "values(@user_account, @user_password, @user_auth, @insert_uid, @insert_date); ";
                sql += "declare @tmp_uid int; set @tmp_uid = (select uid from user_login where u_account=@user_account); ";
                sql += "insert into user_info(uid, u_did, u_name, u_hire_flag, u_sex, u_birthday, u_salary, u_description, u_pid, u_insert_uid, u_insert_date) " +
                    "values(@tmp_uid, @user_department, @u_name, @user_hire, @user_sex, @user_birthday, @user_salary, @user_description, @user_position, @insert_uid, @insert_date)";

                para_list.Add(new SqlParameter("@user_account", m_user_info.user_account));
                para_list.Add(new SqlParameter("@user_password", "111111"));
                para_list.Add(new SqlParameter("@user_auth", m_user_info.user_auth.ToString()));
                para_list.Add(new SqlParameter("@insert_uid", CurrentUser.user_id));
                para_list.Add(new SqlParameter("@insert_date", DateTime.Now));

                para_list.Add(new SqlParameter("@user_department", m_user_info.user_department));
                para_list.Add(new SqlParameter("@u_name", m_user_info.user_name));
                para_list.Add(new SqlParameter("@user_position", m_user_info.user_position));
                para_list.Add(new SqlParameter("@user_sex", m_user_info.user_sex));
                para_list.Add(new SqlParameter("@user_hire", m_user_info.user_hire));
                para_list.Add(new SqlParameter("@user_birthday", m_user_info.user_birthday));
                para_list.Add(new SqlParameter("@user_salary", m_user_info.user_salary));
                para_list.Add(new SqlParameter("@user_description", m_user_info.user_description));
                Logger.WriteSqlLog(sql, para_list);
                if (db_op.ExecuteNonQuery(sql, para_list, true) < 0)
                {
                    MessageBox.Show("添加用户失败");
                    db_op.CloseDB();
                    this.Close();
                    return;
                }
                //get uid from table user_login
                para_list.Clear();
                sql = "select uid from user_login where u_account=@user_account";
                para_list.Add(new SqlParameter("@user_account", m_user_info.user_account));
                Logger.WriteSqlLog(sql, para_list);
                if (db_op.ExecuteDataTable(sql, para_list, false, ref dt) < 0)
                {
                    Logger.WriteLog("fail to get uid from user_login");
                    db_op.CloseDB();
                    this.Close();
                    return;
                }
                m_user_info.uid = dt.Rows[0]["uid"].ToString();
                //update department info if the inserted user is a manager
                if (m_user_info.user_auth == "MANAGER" || m_user_info.user_auth == "BOSS")
                {
                    para_list.Clear();
                    sql = "update department set d_uid=@dpt_manager, d_update_uid=@update_id, d_update_date=@update_date where did=@did";
                    para_list.Add(new SqlParameter("@did", m_user_info.user_department));
                    para_list.Add(new SqlParameter("@dpt_manager", m_user_info.uid));
                    para_list.Add(new SqlParameter("@update_id", CurrentUser.user_id));
                    para_list.Add(new SqlParameter("@update_date", DateTime.Now));
                    if (db_op.ExecuteNonQuery(sql, para_list, false) < 0)
                    {
                        MessageBox.Show("更新部门信息失败");
                        db_op.CloseDB();
                        this.Close();
                        return;
                    }
                }
                MessageBox.Show("添加用户成功");
            }
            else
            {
                //update user_login
                dt.Clear();
                para_list.Clear();
                //use transaction
                sql = "update user_login set u_account=@user_account, u_auth=@user_auth, u_update_uid=@update_id, u_update_date=@update_date " +
                    "where uid=@uid; ";
                sql += "update user_info set u_did=@user_department, u_name=@u_name, u_pid=@user_position, u_hire_flag=@user_hire, u_sex=@user_sex, u_birthday=@user_birthday, u_salary=@user_salary, u_description=@user_description, u_update_uid=@update_id, u_update_date=@update_date " +
                    "where uid=@uid";

                para_list.Add(new SqlParameter("@user_account", m_user_info.user_account));
                para_list.Add(new SqlParameter("@user_auth", m_user_info.user_auth.ToString()));
                para_list.Add(new SqlParameter("@update_id", CurrentUser.user_id));
                para_list.Add(new SqlParameter("@update_date", DateTime.Now));
                para_list.Add(new SqlParameter("@uid", m_user_info.uid));

                para_list.Add(new SqlParameter("@user_department", m_user_info.user_department));
                para_list.Add(new SqlParameter("@u_name", m_user_info.user_name));
                para_list.Add(new SqlParameter("@user_position", m_user_info.user_position));
                para_list.Add(new SqlParameter("@user_sex", m_user_info.user_sex));
                para_list.Add(new SqlParameter("@user_hire", m_user_info.user_hire));
                para_list.Add(new SqlParameter("@user_birthday", m_user_info.user_birthday));
                para_list.Add(new SqlParameter("@user_salary", m_user_info.user_salary));
                para_list.Add(new SqlParameter("@user_description", m_user_info.user_description));
                Logger.WriteSqlLog(sql, para_list);
                if (db_op.ExecuteNonQuery(sql, para_list, true) < 0)
                {
                    MessageBox.Show("修改用户失败");
                    db_op.CloseDB();
                    this.Close();
                    return;
                }
                //update department info if the user is udpated as a manager
                if (m_user_info.user_auth == "MANAGER" || m_user_info.user_auth == "BOSS")
                {
                    para_list.Clear();
                    sql = "update department set d_uid=@dpt_manager, d_update_uid=@update_id, d_update_date=@update_date where did=@did";
                    para_list.Add(new SqlParameter("@did", m_user_info.user_department));
                    para_list.Add(new SqlParameter("@dpt_manager", m_user_info.uid));
                    para_list.Add(new SqlParameter("@update_id", CurrentUser.user_id));
                    para_list.Add(new SqlParameter("@update_date", DateTime.Now));
                    if (db_op.ExecuteNonQuery(sql, para_list, false) < 0)
                    {
                        MessageBox.Show("更新部门信息失败");
                        db_op.CloseDB();
                        this.Close();
                        return;
                    }
                }
                MessageBox.Show("修改用户成功");
            }
            db_op.CloseDB();
            if (OnChange != null)
                OnChange(sender, e);
            this.Close();
        }

        /// <summary>
        /// 清空
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void buttonX2_Click(object sender, EventArgs e)
        {
            Common.CommonDefine.clear_values(this.panelEx1);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void buttonX3_Click(object sender, EventArgs e)
        {
            DialogResult dr = MessageBox.Show("确定删除?", "删除员工", MessageBoxButtons.OKCancel);
            if (dr == DialogResult.OK)
            {
                string uid = m_user_info.uid;
                DBOperator db_op = new DBOperator();
                if (db_op.ConnectDB() < 0)
                {
                    MessageBox.Show("无法连接服务器，请检查网络连接");
                    return;
                }
                List<SqlParameter> para_list = new List<SqlParameter>();
                string sql_users = "delete from user_login where uid=@users_uid";
                para_list.Add(new SqlParameter("@users_uid", uid));

                string sql_userinfo = "delete from user_info where uid=@userinfo_uid";
                para_list.Add(new SqlParameter("@userinfo_uid", uid));

                string sql_userstandard = "delete from standard where s_uid=@userstandard_uid";
                para_list.Add(new SqlParameter("@userstandard_uid", uid));

                string sql_workinfo = "delete from work_info where w_uid=@workinfo_uid";
                para_list.Add(new SqlParameter("@workinfo_uid", uid));

                string sql = sql_users + ";" + sql_userinfo + ";" + sql_userstandard + ";" + sql_workinfo;
                if (db_op.ExecuteNonQuery(sql, para_list, true) < 0)
                {
                    MessageBox.Show("删除失败");
                }
                db_op.CloseDB();
                //TODO whether we should update the uid colume of other tables which uid is not primary key???
            }
        }     

        public delegate void UserInfoChange(object sender, EventArgs e);
        public event UserInfoChange OnChange;
        private UserInfo m_user_info;
        private bool m_modify_mode;
    }

    class UserInfo
    {
        public UserInfo()
        {
            uid = "";
            user_name = "";
            user_account = "";
            user_auth = "";
            user_hire = "";
            user_sex = "";
            user_position = "";
            user_department = "";
            user_birthday = DateTime.Now;
            user_salary = "";
            user_description = "";
            dpt_name = "";
            position_name = "";
        }

        public string uid;
        public string user_name;
        public string user_account;
        public string user_auth;
        public string user_hire;
        public string user_sex;
        public string user_position;
        public string user_department;
        public DateTime user_birthday;
        public string user_salary;
        public string user_description;
        public string dpt_name;
        public string position_name;
    }
}
